﻿-- ================================================
--作者:   王飞
--创建日期:   2012年5月17日
--修改日期：
--版本号：　V1.0
--描述:  分页查询 新闻信息
--所属数据库:PlayMessage
--备注:
-- declare @TotalCount int; exec  proc_GetNewsShowsInfoPageList_Select @SortFieldName=null, @NewsTitle='10岁',@TotalCount=@TotalCount out
-- ================================================
--drop PROCEDURE [proc_GetNewsShowsInfoPageList_Select]

CREATE PROCEDURE [proc_GetNewsShowsInfoPageList_Select]
	@PageSize int=15,
    @PageIndex int=1,
    @SortFieldName nvarchar(50), --排序字段
    @SortDirection INT=0,	         --排序方向,
	@NewsTitle nvarchar(50)=null,		--新闻标题
	@TypeId int =null					--类型..
	--@TotalCount int output
AS
	
	declare @sql nvarchar(max), @SortFieldNameSortDirection nvarchar(50);
	set @SortFieldNameSortDirection=isnull(@SortFieldName,'ID')+ltrim(@SortDirection);
	set @sql=
	'
		with cte as
		(SELECT 
			 ns.Id, ns.TypeId, 
			 pnt.Title AS TypeName,
			 Keyword, NewsTitle, NewsContent, NewsInTime, NewsClick,
			 NewsTag, NewsState, NewsRemark, CreateTime, CreateUser, 
			 LastModifyTime, LastModifyUser,
			case '''+@SortFieldNameSortDirection+'''
				 		WHEN ''CreateTime0'' THEN ROW_NUMBER() OVER (ORDER BY ns.CreateTime DESC) 
						WHEN ''CreateTime1'' THEN ROW_NUMBER() OVER (ORDER BY ns.CreateTime) 
						WHEN ''ID0'' THEN ROW_NUMBER() OVER (ORDER BY ns.ID DESC) 
						WHEN ''ID1'' THEN ROW_NUMBER() OVER (ORDER BY ns.ID) 
						ELSE  ROW_NUMBER() OVER (ORDER BY ns.ID DESC)
						END AS Rn

		 FROM pm_newsShows ns
		 LEFT JOIN pm_newTypes pnt ON ns.TypeId=pnt.Id
		 WHERE (@NewsTitle is null or ns.NewsTitle LIKE N''%''+@NewsTitle+''%'')
		 AND (@TypeId is null or ns.TypeId=@TypeId)
		 ) 
		SELECT  r.*,
		(SELECT CONVERT(BIGINT,COUNT(1)) FROM cte) as TotalCount
		 FROM cte r
	 WHERE r.Rn between (@PageIndex-1)*@PageSize+1 and @PageIndex*@PageSize;
	 '
	 PRINT @sql
	exec sp_executesql @sql,N'@PageSize int, @PageIndex int,@NewsTitle nvarchar(50),@TypeId int',
	 @PageSize,@PageIndex,@NewsTitle,@TypeId

	go